#!/usr/bin/env python
#coding=utf8
"""
@描述：sqlite作为嵌入式关系型数据库，使用比较方便
@作者：范红星
@日期：2014年11月27日 星期四
"""

import sqlite3
import sys
import time

def createFunc(conn,createSql):
	try:
		conn.execute(createSql)
	except Exception,e:
		print(e)
		#print("%s\t%s" %(createSql,"Is Error!!!"))
		#return false

# insert;values是一个数组
def insertFunc(conn,insertSql,values):
	try:
		conn.execute(insertSql,values)
		conn.commit() #每次曾 改 删都要显示指定commit
	except Exception,e:
		print(e)
		#print("%s\t%s\t%t" %(insertSql,"Is Error!!!",",".join(values)))
		#return false;

def selectFunc(conn,selectSql):
	try:
		cur = conn.execute(selectSql)
		for cols in cur.fetchall():
			print("%s\t%s\t%s\t%s" %(cols[0],cols[1],cols[2],cols[3]))
	except Exception,e:
		print(e)
		#print("%s\t%s" %(selectSql,"Is Error!!!"))
		#sys.exit(0)


conn = None
if __name__ == "__main__":
	dbPath = "/home/hongxingfan/sqlite/database.db" #数据库地址
	try:
		conn = sqlite3.connect(dbPath)
	except Exception,e:
		#print("sqlite3.connect dbPath is error!!!")
		print(e)
	#创建表
	createSql = "create table student(id int,name varchar,age int,addr varchar)"
	createFunc(conn,createSql)
	
	#插入；注意中文字符的'u'表示
	insertSql = "insert into student values (?,?,?,?)"
	l_values = [(1,'hongxingfan',27,u'吉林-白城'),(2,'wenxiubi',25,u'黑龙江-亚布力')]
	for values in l_values:
		insertFunc(conn,insertSql,values)
	
	#查询
	selectSql = "select id,name,age,addr from student"
	selectFunc(conn,selectSql)

	#删除，更改等

	time.sleep(20)
	conn.close()
